﻿using System;
using Finisar.SQLite;

namespace MyMovies
{
    internal class TableAudioFileInfo
    {
        private static string create = @"CREATE TABLE [AudioFileInfo] (
                                    [ID] INTEGER  NOT NULL,
                                    [Stream] iNTEGER NOT NULL,
                                    [Format] VARCHAR(64)  NULL,
                                    [Language] VARCHAR(64)  NULL,
                                    [SamplingRate] VARCHAR(32)  NULL,
                                    [BitRate] VARCHAR(32)  NULL,
                                    [Channel] INTEGER NULL,
                                    PRIMARY KEY ([ID],[Stream]))";

        public static bool Create()
        {
            return SqlLiteManagement.ExecuteNonQuery(create) >= 0;
        }

        public static bool FindByID(int id, int stream, ref MediaInfoAudio mia)
        {
            SQLiteParameter p1 = new SQLiteParameter("@p1", System.Data.DbType.Int16);
            p1.Value = id;
            SQLiteParameter p2 = new SQLiteParameter("@p2", System.Data.DbType.Int16);
            p2.Value = stream;

            string query = "SELECT [Format], [Language], [SamplingRate], [BitRate], [Channel] FROM [AudioFileInfo] WHERE [ID] = @p1 and [Stream] = @p2";
            if (SqlLiteManagement.ExecuteReader(query, new SQLiteParameter[] { p1, p2 }) != null)
            {
                if (SqlLiteManagement.Read())
                {
                    mia.Format = SqlLiteManagement.GetValue<string>("[Format]");
                    mia.Language = SqlLiteManagement.GetValue<string>("[Language]");
                    mia.SamplingRate = SqlLiteManagement.GetValue<string>("[SamplingRate]");
                    mia.BitRate = SqlLiteManagement.GetValue<string>("[BitRate]");
                    mia.Channel = SqlLiteManagement.GetValue<int>("[Channel]");
                    SqlLiteManagement.ReaderClose();
                    return true;
                }
                SqlLiteManagement.ReaderClose();
                return false;
            }
            return false;
        }

        public static bool Insert(int id, int stream, MediaInfoAudio mia)
        {
            SQLiteParameter pFormat = new SQLiteParameter("@pFormat", System.Data.DbType.String);
            SQLiteParameter pLanguage = new SQLiteParameter("@pLanguage", System.Data.DbType.String);
            SQLiteParameter pSampling = new SQLiteParameter("@pSampling", System.Data.DbType.String);
            SQLiteParameter pBitRate = new SQLiteParameter("@pBitRate", System.Data.DbType.String);
            SQLiteParameter pChannel = new SQLiteParameter("@pChannel", System.Data.DbType.Int16);
            SQLiteParameter pID = new SQLiteParameter("@pID", System.Data.DbType.Int16);
            SQLiteParameter pStream = new SQLiteParameter("@pStream", System.Data.DbType.Int16);

            pFormat.Value = mia.Format;
            pLanguage.Value = mia.Language;
            pSampling.Value = mia.SamplingRate;
            pBitRate.Value = mia.BitRate;
            pChannel.Value = mia.Channel;
            pID.Value = id;
            pStream.Value = stream;

            string query = "INSERT INTO [AudioFileInfo] ([ID], [Stream], [Format], [Language], [SamplingRate], [BitRate], [Channel]) VALUES (@pID, @pStream,  @pFormat, @pLanguage, @pSampling, @pBitRate, @pChannel)";
            int result = SqlLiteManagement.ExecuteNonQuery(query, new SQLiteParameter[] { pID, pStream, pFormat, pLanguage, pSampling, pBitRate, pChannel });

            return result != 0;
        }

        public static void RemoveOld()
        {
            string query = "DELETE FROM [AudioFileInfo] WHERE [ID] NOT IN (SELECT [ID] FROM [Movies])";
            SqlLiteManagement.ExecuteNonQuery(query);
        }

        public static void RemoveByID(int id)
        {
            string query = "DELETE FROM [AudioFileInfo] WHERE [ID] = @p1";
            SQLiteParameter p1 = new SQLiteParameter("@p1", System.Data.DbType.Int16);
            p1.Value = id;
            SqlLiteManagement.ExecuteNonQuery(query, new SQLiteParameter[] { p1 });
        }

        public static void Fill(System.Windows.Forms.ListView.ListViewItemCollection items, int ID)
        {
            MediaInfoAudio mia = new MediaInfoAudio();
            for (int stream = 0; FindByID(ID, stream, ref mia); stream++ )
            {
                System.Windows.Forms.ListViewItem lvi = null;
                if (stream > 0)
                    items.Add("");
                lvi = new System.Windows.Forms.ListViewItem(Properties.Resources.Format);
                lvi.SubItems.Add(mia.Format);
                items.Add(lvi);

                if (mia.Language != "")
                {
                    lvi = new System.Windows.Forms.ListViewItem(Properties.Resources.Language);
                    lvi.SubItems.Add(Decoder.Decode<string>(mia.Language, Types.Language));
                    items.Add(lvi);
                }

                lvi = new System.Windows.Forms.ListViewItem(Properties.Resources.SamplingRate);
                lvi.SubItems.Add(Decoder.Decode<string>(mia.SamplingRate, Types.SamplingRate));
                items.Add(lvi);

                lvi = new System.Windows.Forms.ListViewItem(Properties.Resources.BitRate);
                lvi.SubItems.Add(Decoder.Decode<string>(mia.BitRate, Types.BitRate));
                items.Add(lvi);

                lvi = new System.Windows.Forms.ListViewItem(Properties.Resources.Channel);
                lvi.SubItems.Add(mia.Channel.ToString());
                items.Add(lvi);
            }
        }
    }
}